﻿/*
=============================================================================
CreatedTime:2011-10-28
Description:Tb_Info三层结构数据访问层
=============================================================================
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using Models;//引入模型层
namespace DAL
{
	public  partial class Tb_InfoServiceBase
	{
		
		#region 表是否存在该记录
		/// <summary>
		/// Tb_Info表是否存在该记录
		/// </summary>
		/// <param name="infoid"></param>
        /// <returns></returns>
		public bool ExistsTb_Info(int infoid)
		{
			StringBuilder strSql = new StringBuilder();
			strSql.Append("Select count(1) from Tb_Info");
			strSql.Append(" where infoid = @infoid");
			SqlParameter[] parameters = {
					new SqlParameter("@infoid", infoid)
				};
			return DBHelper.Exists(strSql.ToString(),parameters);
		}	
		#endregion
		#region 表增加一条数据
		/// <summary>
		/// Tb_Info表增加一条数据
		/// </summary>
		/// <param name="tb_Info">实体对象</param>
        /// <returns></returns>
		public int AddTb_Info(Tb_Info tb_Info)
		{
			string sql =
			"INSERT Tb_Info(catID,title,detail,showimg,datetimeadd,isshow,istj,keyword,description,iscomment,username) "+
			"VALUES (@catID,@title,@detail,@showimg,@datetimeadd,@isshow,@istj,@keyword,@description,@iscomment,@username)";
			
			try
			{
				SqlParameter[] para = new SqlParameter[]
				{
						
					new SqlParameter("@catID",tb_Info.CatID),
						
					new SqlParameter("@title",tb_Info.Title),
						
					new SqlParameter("@detail",tb_Info.Detail),
						
					new SqlParameter("@showimg",tb_Info.Showimg),
						
					new SqlParameter("@datetimeadd",tb_Info.Datetimeadd),
						
					new SqlParameter("@isshow",tb_Info.Isshow),
						
					new SqlParameter("@istj",tb_Info.Istj),
						
					new SqlParameter("@keyword",tb_Info.Keyword),
						
					new SqlParameter("@description",tb_Info.Description),
						
					new SqlParameter("@iscomment",tb_Info.Iscomment),
						
					new SqlParameter("@username",tb_Info.Username)
				};
				int newId = DBHelper.ExecuteCommand(sql, para);
				return newId;
			}
			catch (Exception e)
			{
				Console.WriteLine(e.Message);
				throw e;
			}
		}
		#endregion
	    #region 表删除一条数据
		/// <summary>
		/// Tb_Info表删除一条数据
		/// </summary>
		/// <param name="infoid"></param>
        /// <returns></returns>
			
		public int DeleteTb_InfoByInfoid(int infoid)	
		{
			string sql="DELETE FROM "+"Tb_Info"+" Where Infoid=@infoid";
			try
			{
				SqlParameter[] para = new SqlParameter[]
				{
					new SqlParameter("@infoid", infoid)
				};
				return DBHelper.ExecuteCommand(sql, para);
			}catch(Exception e)
			{
				Console.WriteLine(e.Message);
				throw e;
			}
		}		
		#endregion
		#region 表修改数据
		/// <summary>
		/// Tb_Info表修改数据
		/// </summary>
		/// <param name="tb_Info">实体对象</param>
        /// <returns></returns>
		//考虑标识列与主键不一致情形
		//以标识列修改实体
		public int ModifyTb_Info(Tb_Info tb_Info)
		{
		string sql ="UPDATE "+"Tb_Info"+" set "+
					"catID=@catID, "+
					"title=@title, "+
					"detail=@detail, "+
					"showimg=@showimg, "+
					"datetimeadd=@datetimeadd, "+
					"isshow=@isshow, "+
					"istj=@istj, "+
					"keyword=@keyword, "+
					"description=@description, "+
					"iscomment=@iscomment, "+
					"username=@username "+ 
				" Where Infoid=@Infoid";
			try
			{
				SqlParameter[] para = new SqlParameter[]
				{
					new SqlParameter("@Infoid",tb_Info.Infoid),
					new SqlParameter("@catID",tb_Info.CatID),
					new SqlParameter("@title",tb_Info.Title),
					new SqlParameter("@detail",tb_Info.Detail),
					new SqlParameter("@showimg",tb_Info.Showimg),
					new SqlParameter("@datetimeadd",tb_Info.Datetimeadd),
					new SqlParameter("@isshow",tb_Info.Isshow),
					new SqlParameter("@istj",tb_Info.Istj),
					new SqlParameter("@keyword",tb_Info.Keyword),
					new SqlParameter("@description",tb_Info.Description),
					new SqlParameter("@iscomment",tb_Info.Iscomment),
					new SqlParameter("@username",tb_Info.Username)
				};
				return DBHelper.ExecuteCommand(sql, para);
			}
			catch (Exception e)
			{
				Console.WriteLine(e.Message);
				throw e;
			}
		}					
		#endregion
		#region 查询数据
		#region 通过sql条件获得数据列表
	    /// <summary>
		/// Tb_Info表通过sql条件获得数据列表
		/// </summary>
		/// <param name="StrWhere"></param>
        /// <returns></returns>
		public DataSet GetDataSetFromTb_Info(string strWhere)
		{
			string strSql="SELECT * FROM Tb_Info";
			if(strWhere.Trim()!="")
			{
				strSql +=" where "+strWhere;
			}
			return DBHelper.Query(strSql.ToString());
		}
		#endregion
		#region 得到一个对象实体（泛型集合）
	    /// <summary>
		/// Tb_Info表得到一个对象实体（泛型集合）
		/// </summary>
        /// <returns>IList</returns>
		public IList<Tb_Info> GetAllTb_Infos()
		{
			string sqlAll = "SELECT * FROM Tb_Info";
			return GetTb_InfosBySql(sqlAll);
		}
		#endregion
		#region 得到一个对象实体（不是泛型）
	    /// <summary>
		/// Tb_Info表得到一个对象实体
		/// </summary>
		/// <param name="infoid"></param>
        /// <returns></returns>
		//以标识列获得对象
		public Tb_Info GetTb_InfoByInfoid(int Infoid)
		{
			
			string sql = "SELECT * FROM "+"Tb_Info"+" WHERE Infoid = @Infoid";
			try
			{
				SqlDataReader reader = DBHelper.GetReader(sql, new SqlParameter("@Infoid", Infoid));
				if (reader.Read())
				{
					Tb_Info tb_Info = new Tb_Info();
					if(reader["Infoid"]!=DBNull.Value)
						tb_Info.Infoid=(int)reader["Infoid"];
					if(reader["catID"]!=DBNull.Value)
						tb_Info.CatID=(int)reader["catID"];
					if(reader["title"]!=DBNull.Value)
						tb_Info.Title=(string)reader["title"];
					if(reader["detail"]!=DBNull.Value)
						tb_Info.Detail=(string)reader["detail"];
					if(reader["showimg"]!=DBNull.Value)
						tb_Info.Showimg=(string)reader["showimg"];
					if(reader["datetimeadd"]!=DBNull.Value)
						tb_Info.Datetimeadd=(DateTime)reader["datetimeadd"];
					if(reader["isshow"]!=DBNull.Value)
						tb_Info.Isshow=(int)reader["isshow"];
					if(reader["istj"]!=DBNull.Value)
						tb_Info.Istj=(int)reader["istj"];
					if(reader["keyword"]!=DBNull.Value)
						tb_Info.Keyword=(string)reader["keyword"];
					if(reader["description"]!=DBNull.Value)
						tb_Info.Description=(string)reader["description"];
					if(reader["iscomment"]!=DBNull.Value)
						tb_Info.Iscomment=(int)reader["iscomment"];
					if(reader["username"]!=DBNull.Value)
						tb_Info.Username=(string)reader["username"];
					reader.Close();
					
					return tb_Info;
					}
				else
				{
					reader.Close();
					return null;
				}
			}
			catch(Exception e)
			{
				Console.WriteLine(e.Message);
				throw e;
			}
		}
		#endregion
		#region 通过sql语句得到一个对象实体（泛型集合）
	    /// <summary>
		/// Tb_Info表得到一个实体对象（泛型集合）
		/// </summary>
		/// <param name="safeSql">safeSql</param>
        /// <returns>IList</returns>
		public IList<Tb_Info> GetTb_InfosBySql(string safeSql)
		{
			List<Tb_Info> list = new List<Tb_Info>();
			using (DataTable table = DBHelper.GetDataTable(safeSql))
			{
				foreach (DataRow row in table.Rows)
				{
					Tb_Info tb_Info = new Tb_Info();
					if(row["Infoid"]!=DBNull.Value)
					tb_Info.Infoid=(int)row["Infoid"];
					if(row["catID"]!=DBNull.Value)
					tb_Info.CatID=(int)row["catID"];
					if(row["title"]!=DBNull.Value)
					tb_Info.Title=(string)row["title"];
					if(row["detail"]!=DBNull.Value)
					tb_Info.Detail=(string)row["detail"];
					if(row["showimg"]!=DBNull.Value)
					tb_Info.Showimg=(string)row["showimg"];
					if(row["datetimeadd"]!=DBNull.Value)
					tb_Info.Datetimeadd=(DateTime)row["datetimeadd"];
					if(row["isshow"]!=DBNull.Value)
					tb_Info.Isshow=(int)row["isshow"];
					if(row["istj"]!=DBNull.Value)
					tb_Info.Istj=(int)row["istj"];
					if(row["keyword"]!=DBNull.Value)
					tb_Info.Keyword=(string)row["keyword"];
					if(row["description"]!=DBNull.Value)
					tb_Info.Description=(string)row["description"];
					if(row["iscomment"]!=DBNull.Value)
					tb_Info.Iscomment=(int)row["iscomment"];
					if(row["username"]!=DBNull.Value)
					tb_Info.Username=(string)row["username"];
					list.Add(tb_Info);
				}
				return list;
			}
		}
		#endregion
		#region 通过sql语句的条件得到一个对象实体（泛型集合）
	    /// <summary>
		/// Tb_Info表通过sql语句的条件（“ｗhere”已经写上）得到一个实体对象（泛型集合）
		/// </summary>
		/// <param name="strWhere">strWhere</param>
        /// <returns>IList</returns>
		public IList<Tb_Info> GetTb_InfosByStrWhere(string strWhere)
		{
			string safeSql="SELECT * FROM Tb_Info";
			if(strWhere.Trim()!="")
			{
				safeSql +=" where "+strWhere;
			}
			List<Tb_Info> list = new List<Tb_Info>();
			using (DataTable table = DBHelper.GetDataTable(safeSql))
			{
				foreach (DataRow row in table.Rows)
				{
					Tb_Info tb_Info = new Tb_Info();
					if(row["Infoid"]!=DBNull.Value)
					tb_Info.Infoid=(int)row["Infoid"];
					if(row["catID"]!=DBNull.Value)
					tb_Info.CatID=(int)row["catID"];
					if(row["title"]!=DBNull.Value)
					tb_Info.Title=(string)row["title"];
					if(row["detail"]!=DBNull.Value)
					tb_Info.Detail=(string)row["detail"];
					if(row["showimg"]!=DBNull.Value)
					tb_Info.Showimg=(string)row["showimg"];
					if(row["datetimeadd"]!=DBNull.Value)
					tb_Info.Datetimeadd=(DateTime)row["datetimeadd"];
					if(row["isshow"]!=DBNull.Value)
					tb_Info.Isshow=(int)row["isshow"];
					if(row["istj"]!=DBNull.Value)
					tb_Info.Istj=(int)row["istj"];
					if(row["keyword"]!=DBNull.Value)
					tb_Info.Keyword=(string)row["keyword"];
					if(row["description"]!=DBNull.Value)
					tb_Info.Description=(string)row["description"];
					if(row["iscomment"]!=DBNull.Value)
					tb_Info.Iscomment=(int)row["iscomment"];
					if(row["username"]!=DBNull.Value)
					tb_Info.Username=(string)row["username"];
					list.Add(tb_Info);
				}
				return list;
			}
		}
		#endregion
		#region 通过sql语句和参数得到一个对象实体（泛型集合）
	    /// <summary>
		/// Tb_Info表通过sql语句和参数得到一个实体对象（泛型集合）
		/// </summary>
		/// <param name="safeSql">safeSql</param>
		/// <param name="SqlParameter[]">params SqlParameter[]</param>
        /// <returns>IList</returns>
		public IList<Tb_Info> GetTb_InfosBySql( string sql, params SqlParameter[] values )
		{
			List<Tb_Info> list = new List<Tb_Info>();
			try
			{
				DataTable table = DBHelper.GetDataTable( sql, values );
				
				foreach (DataRow row in table.Rows)
				{
					Tb_Info tb_Info = new Tb_Info();
					if(row["Infoid"]!=DBNull.Value)
					tb_Info.Infoid=(int)row["Infoid"];
					if(row["catID"]!=DBNull.Value)
					tb_Info.CatID=(int)row["catID"];
					if(row["title"]!=DBNull.Value)
					tb_Info.Title=(string)row["title"];
					if(row["detail"]!=DBNull.Value)
					tb_Info.Detail=(string)row["detail"];
					if(row["showimg"]!=DBNull.Value)
					tb_Info.Showimg=(string)row["showimg"];
					if(row["datetimeadd"]!=DBNull.Value)
					tb_Info.Datetimeadd=(DateTime)row["datetimeadd"];
					if(row["isshow"]!=DBNull.Value)
					tb_Info.Isshow=(int)row["isshow"];
					if(row["istj"]!=DBNull.Value)
					tb_Info.Istj=(int)row["istj"];
					if(row["keyword"]!=DBNull.Value)
					tb_Info.Keyword=(string)row["keyword"];
					if(row["description"]!=DBNull.Value)
					tb_Info.Description=(string)row["description"];
					if(row["iscomment"]!=DBNull.Value)
					tb_Info.Iscomment=(int)row["iscomment"];
					if(row["username"]!=DBNull.Value)
					tb_Info.Username=(string)row["username"];
					list.Add(tb_Info);
				}
				return list;
			}
			catch (Exception e)
			{
				Console.WriteLine(e.Message);
				throw e;
			}	
		}
		#endregion
		
		#region 接收条件和参数Hashtable 返回DataTable
		/// <summary>
		/// Tb_Info表接收条件和参数Hashtable，返回DataTable
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		public DataTable GetDataTableFromTb_Info(string strWhere,Hashtable table)
		{
			string strSql = "select * from Tb_Info where strWhere ";
			List<SqlParameter> parmater = new List<SqlParameter>();
			foreach(DictionaryEntry entry in table)
			{
				SqlParameter para = new SqlParameter(entry.Key.ToString(), entry.Value);
				parmater.Add(para);
			}
			return DBHelper.Query(strSql.ToString(), parmater.ToArray()).Tables[0];
		}
		#endregion
		#region 接收条件和参数Hashtable 返回List泛型集合
		/// <summary>
		/// Tb_Info表接收条件和参数Hashtable，返回List泛型集合
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		public IList<Tb_Info> GetListFromTb_Info(string strWhere, Hashtable table)
		{
			List<Tb_Info> models = new List<Tb_Info>();
			string strSql = "select * from Tb_Info where strWhere ";
			List<SqlParameter> parmater = new List<SqlParameter>();
			foreach(DictionaryEntry entry in table)
			{
				SqlParameter para = new SqlParameter(entry.Key.ToString(), entry.Value);
				parmater.Add(para);
			}
			using (SqlDataReader dataReader = DBHelper.GetReader(strSql.ToString(),parmater.ToArray()))
			{
				while (dataReader.Read())
				{
					Tb_Info model = new Tb_Info();
					model.Infoid = Convert.ToInt32(dataReader["Infoid"]);
					model.CatID = Convert.ToInt32(dataReader["catID"]);
					model.Title = Convert.ToString(dataReader["title"]);
					model.Detail = Convert.ToString(dataReader["detail"]);
					model.Showimg = Convert.ToString(dataReader["showimg"]);
					model.Datetimeadd = Convert.ToDateTime(dataReader["datetimeadd"]);
					model.Isshow = Convert.ToInt32(dataReader["isshow"]);
					model.Istj = Convert.ToInt32(dataReader["istj"]);
					model.Keyword = Convert.ToString(dataReader["keyword"]);
					model.Description = Convert.ToString(dataReader["description"]);
					model.Iscomment = Convert.ToInt32(dataReader["iscomment"]);
					model.Username = Convert.ToString(dataReader["username"]);
					models.Add(model);
				}
			}
			return models;
		}
		#endregion
		#region 接收条件和参数Hashtable，返回一个实体类对象
		/// <summary>
		/// Tb_Info表接收条件和参数Hashtable，返回一个实体类对象
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		public Tb_Info GetModelFromTb_InfoByWhere(string strWhere, Hashtable table)
		{
			Tb_Info model = new Tb_Info();
			string strSql="select * from Tb_Info where "+strWhere+" ";
			List<SqlParameter> parmater = new List<SqlParameter>();
			foreach(DictionaryEntry entry in table)
			{
				SqlParameter para = new SqlParameter(entry.Key.ToString(), entry.Value);
				parmater.Add(para);
			}
			using (SqlDataReader dataReader = DBHelper.GetReader(strSql.ToString(), parmater.ToArray()))
			{
				while (dataReader.Read())
				{
					model.Infoid = Convert.ToInt32(dataReader["Infoid"]);
					model.CatID = Convert.ToInt32(dataReader["catID"]);
					model.Title = Convert.ToString(dataReader["title"]);
					model.Detail = Convert.ToString(dataReader["detail"]);
					model.Showimg = Convert.ToString(dataReader["showimg"]);
					model.Datetimeadd = Convert.ToDateTime(dataReader["datetimeadd"]);
					model.Isshow = Convert.ToInt32(dataReader["isshow"]);
					model.Istj = Convert.ToInt32(dataReader["istj"]);
					model.Keyword = Convert.ToString(dataReader["keyword"]);
					model.Description = Convert.ToString(dataReader["description"]);
					model.Iscomment = Convert.ToInt32(dataReader["iscomment"]);
					model.Username = Convert.ToString(dataReader["username"]);
				}
			}
			return model;
		}
		#endregion
		#region 接收条件，返回一个实体类对象
		/// <summary>
		/// Tb_Info表接收条件，返回一个实体类对象
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		public Tb_Info GetModelFromTb_InfoByWhere(string strWhere)
		{
			Tb_Info model = new Tb_Info();
			string strSql="select * from Tb_Info where "+strWhere+" ";
			using (SqlDataReader dataReader = DBHelper.GetReader(strSql.ToString()))
			{
				while (dataReader.Read())
				{
					if(dataReader["Infoid"]!=DBNull.Value)
					model.Infoid = Convert.ToInt32(dataReader["Infoid"]);
					if(dataReader["catID"]!=DBNull.Value)
					model.CatID = Convert.ToInt32(dataReader["catID"]);
					if(dataReader["title"]!=DBNull.Value)
					model.Title = Convert.ToString(dataReader["title"]);
					if(dataReader["detail"]!=DBNull.Value)
					model.Detail = Convert.ToString(dataReader["detail"]);
					if(dataReader["showimg"]!=DBNull.Value)
					model.Showimg = Convert.ToString(dataReader["showimg"]);
					if(dataReader["datetimeadd"]!=DBNull.Value)
					model.Datetimeadd = Convert.ToDateTime(dataReader["datetimeadd"]);
					if(dataReader["isshow"]!=DBNull.Value)
					model.Isshow = Convert.ToInt32(dataReader["isshow"]);
					if(dataReader["istj"]!=DBNull.Value)
					model.Istj = Convert.ToInt32(dataReader["istj"]);
					if(dataReader["keyword"]!=DBNull.Value)
					model.Keyword = Convert.ToString(dataReader["keyword"]);
					if(dataReader["description"]!=DBNull.Value)
					model.Description = Convert.ToString(dataReader["description"]);
					if(dataReader["iscomment"]!=DBNull.Value)
					model.Iscomment = Convert.ToInt32(dataReader["iscomment"]);
					if(dataReader["username"]!=DBNull.Value)
					model.Username = Convert.ToString(dataReader["username"]);
				}
			}
			return model;
		}
		#endregion
		
		#region 接收条件和参数Hashtable，删除符合条件的记录
		/// <summary>
		/// Tb_Info表接收条件和参数Hashtable，删除符合条件的记录
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		
		public int DeleteTb_InfoByWhere(string strWhere)
		{
			string strSql="Delete From Tb_Info where "+strWhere+" ";
			return DBHelper.ExecuteCommand(strSql.ToString());
		}
		/// <summary>
		/// Tb_Info表接收条件，删除符合条件的记录
		/// </summary>
		/// <param name="StrWhere"></param>
		/// <param name="table"></param>
        /// <returns></returns>
		
		public int DeleteTb_InfoByWhere(string strWhere, Hashtable table)
		{
			string strSql="Delete From Tb_Info where "+strWhere+" ";
			List<SqlParameter> parmater = new List<SqlParameter>();
			foreach(DictionaryEntry entry in table)
			{
				SqlParameter para = new SqlParameter(entry.Key.ToString(), entry.Value);
				parmater.Add(para);
			}
			return DBHelper.ExecuteCommand(strSql.ToString(), parmater.ToArray());
		}
		#endregion
		#endregion
		#region 分页的方法page为当前页码，NUMCount为每页数据
		
		/// <summary>
		/// 分页的方法page为当前页码，NUMCount为每页数据
		/// </summary>
		/// <param name="page">为当前页码</param>
		/// <param name="NUMCount">为每页数据</param>
        /// <returns></returns>
		public IList<Tb_Info> ShowTb_InfoForPage(int curragePage,int NUMCount)
		{
			int number = 0;
			if (curragePage > 1) {
				number = NUMCount * (curragePage - 1);
			}
			string strsql= "select top "
						   +NUMCount+" * from Tb_Info where  Infoid not in ( select top "
						   +number+" Infoid from Tb_Info order by Infoid desc ) order by Infoid desc";
			return GetTb_InfosBySql(strsql);
		}
		#endregion
	}
}

